<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Introduction</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="C#, SQLite, ADO.NET, database, tutorial, learn SQLite C# ">
<meta name="description" content="This is an introductory chapter of the SQLite C# tutorial.
    It provides some basic definitions, prerequisites and first working examples.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Introduction</h1>


<p>
In the first chapter of the SQLite C# tutorial, we will provide
necessary definitions. We will show, how to install Mono. All the
examples in this tutorial will be run on Mono. Later we create 
the first working examples.
</p>


<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* LargeSquare */
google_ad_slot = "5070696322";
google_ad_width = 336;
google_ad_height = 280;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>


<h2>About SQLite database</h2>

<p>
<b>SQLite</b> is an embedded relational database engine. Its developers call it a 
self-contained, serverless, zero-configuration and transactional SQL database 
engine. It is very popular and there are hundreds of millions copies worldwide 
in use today. SQLite is used in Solaris 10 and Mac OS operating systems, iPhone 
or Skype. Qt4 library has a buit-in support for the SQLite as well as the Python 
or the PHP language. Many popular applications use SQLite internally such as 
Firefox or Amarok.
</p>

<pre>
$ sudo apt-get install sqlite3
</pre>

<p>
We need to install sqlite3 library, if it is not installed already.
</p>

<p>
The SQLite comes with the sqlite3 command line utility. It can be used to
issue SQL commands against a database. Now we are going to use the sqlite3 command 
line tool to create a new database. 
</p>

<pre>
$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
</pre>

<p>
We provide a parameter to the sqlite3 tool. The test.db is a 
database name. It is a single file on our disk. If it is present, 
it is opened. If not, it is created. 
</p>


<pre>
sqlite> .tables
sqlite> .exit
$ ls
test.db
</pre>

<p>
The <code>.tables</code> command gives a list of tables in the test.db
database. There are currently no tables. The <code>.exit</code> command
terminates the interactive session of the sqlite3 command line tool. 
The ls Unix command shows the contents of the current working directory. 
We can see the test.db file. All data will be stored in this single file.
</p>


<h2>Mono</h2>

<p>
Mono is an open source implementation of Microsoft's .NET Framework based 
on the ECMA standards for C# and the Common Language Runtime. We need
to have Mono installed in order to compile and run the examples, in this
tutorial.
</p>

<p>
Mono can be installed from the packages of our Linux distribution or we
can install Mono from sources to get more up-to-date version.
</p>

<pre>
$ bunzip2 mono-2.10.8.tar.bz2
$ tar -xf mono-2.10.8.tar
$ cd mono-2.10.8/
$ ./configure
$ make
$ sudo make install
</pre>

<p>
We download the mono-2.10.8.tar.bz2 tarball from the Mono website. Uncompress it,
build and install the libraries. We install the Mono runtime, C# language and
the SQLite C# data adapter among others. 
</p>

<pre>
$ bunzip2 libgdiplus-2.10.9.tar.bz2 
$ tar -xf libgdiplus-2.10.9.tar 
$ cd libgdiplus-2.10.9/
$ ./configure
$ make
$ sudo make install
</pre>

<p>
For the example with the Winforms control, we need also the libgdiplus library.
It is located in a separate file. We build and install it. 
</p>

<pre>
$ sudo ldconfig
$ ldconfig -p | grep libgdiplus
        libgdiplus.so.0 (libc6) => /usr/local/lib/libgdiplus.so.0
        libgdiplus.so (libc6) => /usr/local/lib/libgdiplus.so
</pre>

<p>
We also run the ldconfig tool to update the database of dynamic libraries.
The ldconfig scans a running system and sets up the symbolic links that 
are used to load shared libraries.
</p>

<hr class="btm">

<p>
The <b>Mono.Data.Sqlite</b> assembly contains an ADO.NET data provider for the SQLite
database. It is written in C# and is available for all CLI languages. C#, Visual Basic, 
Boo and others. 
</p>

<pre>
$ ls /usr/local/lib/mono/4.0/Mono.Data.Sqlite.dll 
/usr/local/lib/mono/4.0/Mono.Data.Sqlite.dll
</pre>

<p>
From the technical point of view, we need a dll. On our system, it
was located under the above path. (In fact the above is a soft link to the dll, which
is located in a gac subdirectory.) 
</p>


<h2>ADO.NET</h2>

<p>
<b>ADO.NET</b> is an important part of the .NET framework. It is a specification
that unifies access to relational databases, XML files and other application data.
From the programmer's point of view it is a set of libraries 
and classes to work with database and other data sources.
A <b>Mono.Data.SQLite</b> is an implementation of the ADO.NET specification
for the SQLite database. It is a driver written in C# language and is available for
all .NET languages. 
</p>

<p>
SqliteConnection, SqliteCommand, SqliteDataReader, SqliteDataAdapter
are the core elements of the .NET data provider model. The <b>SqliteConnection</b> 
creates a connection to a specific data source. The <b>SqliteCommand</b> object 
executes an SQL statement against a data source. The <b>SqliteDataReader</b> reads 
streams of data from a  data source. A <b>SqliteDataAdapter</b> is an intermediary 
between the DataSet and the data source. It populates a DataSet and resolves 
updates with the data source.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<p>
The <b>DataSet</b> object is used for offline work with a mass of data. It is a 
disconnected data representation that can hold data from a variety of different 
sources. Both SqliteDataReader and DataSet are used to work with data; they are used under
different circumstances. If we only need to read the results of a query, the 
SqliteDataReader is the better choice. If we need more extensive processing of data, 
or we want to bind a Winforms control to a database table, the DataSet is preferred.
</p>


<h2>SQLite version</h2>

<p>
If the first program, we check the version of the SQLite database. 
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "Data Source=:memory:";

        SqliteConnection con = null;
        SqliteCommand cmd = null;

        try 
        {
            con = new SqliteConnection(cs);
            con.Open();

            string stm = "SELECT SQLITE_VERSION()";   
            cmd = new SqliteCommand(stm, con);

            string version = Convert.ToString(cmd.ExecuteScalar());

            Console.WriteLine("SQLite version : {0}", version);
            
        } catch (SqliteException ex) 
        {
            Console.WriteLine("Error: {0}",  ex.ToString());

        } finally 
        {   
            if (cmd != null)
            {
                cmd.Dispose();
            }
         
            if (con != null) 
            {
                try 
                {
                    con.Close();

                } catch (SqliteException ex)
                { 
                    Console.WriteLine("Closing connection failed.");
                    Console.WriteLine("Error: {0}",  ex.ToString());
                    
                } finally 
                {
                    con.Dispose();
                }
            }                        
        }
    }
}
</pre>

<p>
We connect to an in-memory database and select an SQLite version.
</p>

<pre class="explanation">
using Mono.Data.Sqlite;
</pre>

<p>
The Mono.Data.SqliteClient assembly contains an ADO.NET data provider 
for the SQLite database engine. We import the elements of the SQLite
data provider. 
</p>

<pre class="explanation">
string cs = "Data Source=:memory:";
</pre>

<p>
This is the connection string. It is used by the data provider
to establish a connection to the database. We create an in-memory
database.
</p>

<pre class="explanation">
con = new SqliteConnection(cs);
</pre>

<p>
A <code>SqliteConnection</code> object is created. This object is used to
open a connection to a database. 
</p>

<pre class="explanation">
con.Open();
</pre>

<p>
This line opens the database connection. 
</p>

<pre class="explanation">
string stm = "SELECT SQLITE_VERSION()";   
</pre>

<p>
This is the SQL SELECT statement. It returns the version of the database. 
The SQLITE_VERSION() is a built-in SQLite function.
</p>

<pre class="explanation">
SqliteCommand cmd = new SqliteCommand(stm, con);
</pre>

<p>
The <code>SqliteCommand</code> is an object, which is used to execute a 
query on the database. The parameters are the SQL statement and the connection object.
</p>

<pre class="explanation">
string version = Convert.ToString(cmd.ExecuteScalar());
</pre>

<p>
There are queries which return only a scalar value. In our case, we want a simple 
string specifying the version of the database. The <code>ExecuteScalar()</code> is 
used in such situations. We avoid the overhead of using more complex objects.
</p>

<pre class="explanation">
Console.WriteLine("SQLite version : {0}", version);
</pre>

<p>
The version of the database is printed to the console.
</p>

<pre class="explanation">
} catch (SqliteException ex) 
{
    Console.WriteLine("Error: {0}",  ex.ToString());
</pre>

<p>
In case of an exception, we print the error message to the
console. 
</p>

<pre class="explanation">
} finally 
{   
    if (cmd != null)
    {
        cmd.Dispose();
    }
</pre>

<p>
The <code>SqliteCommand</code> class implements the IDisposable
interface. Therefore it must be explicitly disposed. 
</p>

<pre class="explanation">
if (con != null) 
{
    try 
    {
        con.Close();

    } catch (SqliteException ex)
    { 
        Console.WriteLine("Closing connection failed.");
        Console.WriteLine("Error: {0}",  ex.ToString());
        
    } finally 
    {
        con.Dispose();
    }
}  
</pre>

<p>
Closing connection may throw another exception. We handle this
situation. 
</p>

<pre>
$ dmcs version.cs -r:Mono.Data.Sqlite.dll
</pre>

<p>
We compile our example. A path to the SQLite data provider dll
is provided. 
</p>

<pre>
$ mono ./version.exe 
SQLite version : 3.7.7
</pre>

<p>
This is the output of the program on our system.
</p>


<h2>The using statement</h2>

<p>
The C# language implements garbage collection. It is a process of automatic
release of objects that are no longer required. The process is non-deterministic.
We cannot be sure when the CLR (Common Language Runtime) decides to release
resources. For limited resources such as file handles or network connections 
it is best to release them as quickly as possible. With the <b>using</b> statement, 
the programmer controls when the resource is to be released. When the program is
out of the using block, either reaches the end of it or an exception is thrown,
the resource gets released. 
</p>

<p>
Internally, the using statement is translated into try, finally blocks with a
Dispose() call in the finally block. Note that you might prefer to use try, catch,
finally blocks instead of the using statement. Especially, if you want to utilize
the catch block explicitly. In this tutorial we have chosen the using statement. Mainly
because the code is shorter.
</p>

<p>
As a rule, when we use an <b>IDisposable</b> object, we should declare and 
instantiate it in a using statement. (Or call Dispose() method in the finally block.) 
In the case of the SQLite ADO.NET driver, we use the using statement for the 
SqliteConnection, SqliteCommand, SqliteDataReader, SqliteCommandBuilder and 
SqliteDataAdapter classes. We do not have to use it for DataSet or DataTable 
classes. They can be left for the garbage collector. 
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";

        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using (SqliteCommand cmd = new SqliteCommand(con))
            {
                cmd.CommandText = "SELECT SQLITE_VERSION()";
                string version = Convert.ToString(cmd.ExecuteScalar());

                Console.WriteLine("SQLite version : {0}", version);
            }             
            
            con.Close();
        }
    }
}
</pre>

<p>
We have the same example. This time we implement the using keyword. 
</p>


<pre class="explanation">
using (SqliteConnection con = new SqliteConnection(cs)) 
{
    con.Open();

    using (SqliteCommand cmd = new SqliteCommand(con))
</pre>

<p>
Both <code>SqliteConnection</code> and <code>SqliteCommand</code> implement 
the <code>IDisposable</code> interface. Therefore they are wrapped with the using keyword. 
</p>


<h2>Creating and populating a table</h2>

<p>
Next we are going to create a database table and fill it
with data.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {

        string cs = "URI=file:test.db";

        using ( SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            using (SqliteCommand cmd = new SqliteCommand(con))
            {
                cmd.CommandText = "DROP TABLE IF EXISTS Cars";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
                    Name TEXT, Price INT)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(1,'Audi',52642)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(3,'Skoda',9000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(4,'Volvo',29000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(5,'Bentley',350000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(6,'Citroen',21000)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(7,'Hummer',41400)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Cars VALUES(8,'Volkswagen',21600)";
                cmd.ExecuteNonQuery();
            }             

            con.Close();
        }
    }
}
</pre>

<p>
In the above code example, we create a Cars table with 8 rows.
</p>

<pre class="explanation">
cmd.CommandText = "DROP TABLE IF EXISTS Cars";
cmd.ExecuteNonQuery();
</pre>

<p>
First we drop the table, if it already exists. We can use 
the <code>ExecuteNonQuery()</code> method if we do not want a 
result set. For example for DROP, INSERT or DELETE statements.
</p>

<pre class="explanation">
cmd.CommandText = @"CREATE TABLE Cars(Id INTEGER PRIMARY KEY, 
    Name TEXT, Price INT)";
cmd.ExecuteNonQuery();
</pre>

<p>
A Cars table is created. INTEGER PRIMARY KEY column is 
autoincremented in SQLite. 
</p>

<pre class="explanation">
cmd.CommandText = "INSERT INTO Cars VALUES(1,'Audi',52642)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Cars VALUES(2,'Mercedes',57127)";
cmd.ExecuteNonQuery();
</pre>

<p>
We insert two rows into the table.
</p>

<pre>
sqlite> .mode column  
sqlite> .headers on
</pre>

<p>
In the sqlite3 command line tool we modify the way the data is displayed 
in the console. We use the column mode and turn on the headers.
</p>

<pre>
sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600     
</pre>

<p>
We verify the data. The Cars table was successfully created.
</p>


<h2>Prepared statements</h2>

<p>
Now we will concern ourselves with prepared statements. 
When we write prepared statements, we use placeholders instead 
of directly writing the values into the statements. 
Prepared statements increase security and performance.
</p>


<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {

        string cs = "URI=file:test.db";

        using(SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();

            using (SqliteCommand cmd = new SqliteCommand(con)) 
            {
                cmd.CommandText = "INSERT INTO Cars(Name, Price) VALUES(@Name, @Price)";
                cmd.Prepare();
                
                cmd.Parameters.AddWithValue("@Name", "BMW");
                cmd.Parameters.AddWithValue("@Price", 36600);
                cmd.ExecuteNonQuery();
            }

            con.Close();
        }
    }
}
</pre>

<p>
We add a row to the Cars table. We use a parameterized command.
</p>

<pre class="explanation">
cmd.CommandText = "INSERT INTO Cars(Name, Price) VALUES(@Name, @Price)";
cmd.Prepare();
</pre>

<p>
Here we create a prepared statement. When we write prepared statements, we use 
placeholders instead of directly writing the values into the statements. 
Prepared statements are faster and guard against SQL injection attacks. 
The @Name and @Price are placeholders, which are going to be filled later. 
</p>

<pre class="explanation">
cmd.Parameters.AddWithValue("@Name", "BMW");
cmd.Parameters.AddWithValue("@Price", 36600);
</pre>

<p>
Values are bound to the placeholders. 
</p>

<pre class="explanation">
cmd.ExecuteNonQuery();
</pre>

<p>
The prepared statement is executed. We use the <code>ExecuteNonQuery()</code>
method of the <code>SqliteCommand</code> object when we do not expect any 
data to be returned. 
</p>

<pre>
$ mono prepared.exe 

sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600     
9           BMW         36600  
</pre>

<p>
We have a new car inserted into the table. 
</p>


<h2>Sources</h2>

<p>
The MSDN (Microsoft Developer Network) was consulted to create this tutorial.
Several definitions come from this website. 
</p>


<p>
This was an introductory chapter to SQLite C# tutorial.  
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 18, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

